package com.etcn.common.schedule;

import java.util.List;
import java.util.Map;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import com.etcn.common.utils.DataUtil;
import com.etcn.common.utils.StringUtil;
import ch.qos.logback.classic.Logger;

/**
 * 抓取最新指数信息保存到最新指数表中，用于列表展示
 * @author guozc
 */
@Component
public class FetchNewestExponentSchedule {
    private Logger serviceLogger = (Logger) LoggerFactory.getLogger("service_file_log");
	
	@Autowired
	@Qualifier("staticalJdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	/**
	 * 执行方法
	 * 每天晚上3点执行
	 */
	@Scheduled(cron = "0 0 3 * * ?")
    public void fetch() throws Exception {
		String sql = null;
		
		String goodsExponentTbName = jdbcTemplate.queryForObject("SELECT table_name FROM information_schema.TABLES WHERE table_name "
				+ "like 'wm_goods_exponent_statistics_%' order by table_name desc limit 1", String.class);
		serviceLogger.info("最新商品指数表为：" + goodsExponentTbName);
		if(StringUtil.isNotEmpty(goodsExponentTbName)){
			sql = "select b.hscode,GROUP_CONCAT(b.trade_type,':',b.exponent) exponentinfo from (select hscode,trade_type, MAX(statistic_value) statistic_value "
					+ "from "+goodsExponentTbName+" where statistic_datetype = '1' group by hscode,trade_type) a join "+goodsExponentTbName+" b"
					+ " on a.hscode=b.hscode and a.trade_type = b.trade_type and a.statistic_value = b.statistic_value GROUP BY b.hscode";
			List<Map<String,Object>> l = jdbcTemplate.queryForList(sql);
			if(l != null){
				for(Map<String,Object> m : l){
					String hscode = (String)m.get("hscode");
					String exponentinfo = (String)m.get("exponentinfo");
					String importExponent = "";
					String exportExponent = "";
					if(StringUtil.isNotEmpty(exponentinfo)){
						for(String str : exponentinfo.split(",")){
							String[] strAry = str.split(":");
							if(strAry.length == 2){
								if("1".equals(strAry[0])){
									importExponent = DataUtil.keepTwoDecimals(strAry[1]);
								}else{
									exportExponent = DataUtil.keepTwoDecimals(strAry[1]);
								}
							}
						}
						sql = "select count(1) from wm_list_exponent where type = '1' and type_id = '"+hscode+"'";
						int count = jdbcTemplate.queryForObject(sql, Integer.class);
						if(count > 0){
							sql = "update wm_list_exponent set import_exponent = ?,export_exponent=? where type = '1' and type_id = ?";
							jdbcTemplate.update(sql, new Object[]{importExponent, exportExponent, hscode});
						}else{
							sql = "insert into wm_list_exponent(type,type_id,import_exponent,export_exponent) values(?,?,?,?)";
							jdbcTemplate.update(sql, new Object[]{"1", hscode, importExponent, exportExponent});
						}
					}
				}
			}
		}
		
		String companyExponentTbName = jdbcTemplate.queryForObject("SELECT table_name FROM information_schema.TABLES WHERE table_name "
				+ "like 'wm_company_exponent_statistics_%' order by table_name desc limit 1", String.class);
		serviceLogger.info("最新企业指数表为：" + companyExponentTbName);
		if(StringUtil.isNotEmpty(companyExponentTbName)){
			sql = "select b.company_code,GROUP_CONCAT(b.trade_type,':',b.exponent) exponentinfo from (select company_code,trade_type, MAX(statistic_value) statistic_value "
					+ "from "+companyExponentTbName+" where statistic_datetype = '1' group by company_code,trade_type) a join "+companyExponentTbName+" b"
					+ " on a.company_code=b.company_code and a.trade_type = b.trade_type and a.statistic_value = b.statistic_value GROUP BY b.company_code";
			List<Map<String,Object>> l = jdbcTemplate.queryForList(sql);
			if(l != null){
				for(Map<String,Object> m : l){
					String companyCode = (String)m.get("company_code");
					String exponentinfo = (String)m.get("exponentinfo");
					String importExponent = "";
					String exportExponent = "";
					if(StringUtil.isNotEmpty(exponentinfo)){
						for(String str : exponentinfo.split(",")){
							String[] strAry = str.split(":");
							if(strAry.length == 2){
								if("1".equals(strAry[0])){
									importExponent = DataUtil.keepTwoDecimals(strAry[1]);
								}else{
									exportExponent = DataUtil.keepTwoDecimals(strAry[1]);
								}
							}
						}
						sql = "select count(1) from wm_list_exponent where type = '2' and type_id = '"+companyCode+"'";
						int count = jdbcTemplate.queryForObject(sql, Integer.class);
						if(count > 0){
							sql = "update wm_list_exponent set import_exponent = ?,export_exponent=? where type = '2' and type_id = ?";
							jdbcTemplate.update(sql, new Object[]{importExponent, exportExponent, companyCode});
						}else{
							sql = "insert into wm_list_exponent(type,type_id,import_exponent,export_exponent) values(?,?,?,?)";
							jdbcTemplate.update(sql, new Object[]{"2", companyCode, importExponent, exportExponent});
						}
					}
				}
			}
		}
		
		String areaExponentTbName = jdbcTemplate.queryForObject("SELECT table_name FROM information_schema.TABLES WHERE table_name "
				+ "like 'wm_area_exponent_statistics_%' order by table_name desc limit 1", String.class);
		serviceLogger.info("最新地区指数表为：" + areaExponentTbName);
		if(StringUtil.isNotEmpty(areaExponentTbName)){
			sql = "select b.area_code,GROUP_CONCAT(b.trade_type,':',b.exponent) exponentinfo from (select area_code,trade_type, MAX(statistic_value) statistic_value "
					+ "from "+areaExponentTbName+" where statistic_datetype = '1' group by area_code,trade_type) a join "+areaExponentTbName+" b"
					+ " on a.area_code=b.area_code and a.trade_type = b.trade_type and a.statistic_value = b.statistic_value GROUP BY b.area_code";
			List<Map<String,Object>> l = jdbcTemplate.queryForList(sql);
			if(l != null){
				for(Map<String,Object> m : l){
					String areaCode = (String)m.get("area_code");
					if(areaCode.length() == 6){
						areaCode = areaCode.substring(2);
					}
					String exponentinfo = (String)m.get("exponentinfo");
					String importExponent = "";
					String exportExponent = "";
					if(StringUtil.isNotEmpty(exponentinfo)){
						for(String str : exponentinfo.split(",")){
							String[] strAry = str.split(":");
							if(strAry.length == 2){
								if("1".equals(strAry[0])){
									importExponent = DataUtil.keepTwoDecimals(strAry[1]);
								}else{
									exportExponent = DataUtil.keepTwoDecimals(strAry[1]);
								}
							}
						}
						sql = "select count(1) from wm_list_exponent where type = '3' and type_id = '"+areaCode+"'";
						int count = jdbcTemplate.queryForObject(sql, Integer.class);
						if(count > 0){
							sql = "update wm_list_exponent set import_exponent = ?,export_exponent=? where type = '3' and type_id = ?";
							jdbcTemplate.update(sql, new Object[]{importExponent, exportExponent, areaCode});
						}else{
							sql = "insert into wm_list_exponent(type,type_id,import_exponent,export_exponent) values(?,?,?,?)";
							jdbcTemplate.update(sql, new Object[]{"3", areaCode, importExponent, exportExponent});
						}
					}
				}
			}
		}
		
		String countryExponentTbName = jdbcTemplate.queryForObject("SELECT table_name FROM information_schema.TABLES WHERE table_name "
				+ "like 'wm_country_exponent_statistics_%' order by table_name desc limit 1", String.class);
		serviceLogger.info("最新国家指数表为：" + countryExponentTbName);
		if(StringUtil.isNotEmpty(countryExponentTbName)){
			sql = "select b.country_code,GROUP_CONCAT(b.trade_type,':',b.exponent) exponentinfo from (select country_code,trade_type, MAX(statistic_value) statistic_value "
					+ "from "+countryExponentTbName+" where statistic_datetype = '1' group by country_code,trade_type) a join "+countryExponentTbName+" b"
					+ " on a.country_code=b.country_code and a.trade_type = b.trade_type and a.statistic_value = b.statistic_value GROUP BY b.country_code";
			List<Map<String,Object>> l = jdbcTemplate.queryForList(sql);
			if(l != null){
				for(Map<String,Object> m : l){
					String countryCode = (String)m.get("country_code");
					String exponentinfo = (String)m.get("exponentinfo");
					String importExponent = "";
					String exportExponent = "";
					if(StringUtil.isNotEmpty(exponentinfo)){
						for(String str : exponentinfo.split(",")){
							String[] strAry = str.split(":");
							if(strAry.length == 2){
								if("1".equals(strAry[0])){
									importExponent = DataUtil.keepTwoDecimals(strAry[1]);
								}else{
									exportExponent = DataUtil.keepTwoDecimals(strAry[1]);
								}
							}
						}
						sql = "select count(1) from wm_list_exponent where type = '4' and type_id = '"+countryCode+"'";
						int count = jdbcTemplate.queryForObject(sql, Integer.class);
						if(count > 0){
							sql = "update wm_list_exponent set import_exponent = ?,export_exponent=? where type = '4' and type_id = ?";
							jdbcTemplate.update(sql, new Object[]{importExponent, exportExponent, countryCode});
						}else{
							sql = "insert into wm_list_exponent(type,type_id,import_exponent,export_exponent) values(?,?,?,?)";
							jdbcTemplate.update(sql, new Object[]{"4", countryCode, importExponent, exportExponent});
						}
					}
				}
			}
		}
		
    }

}
